USE [SI80ADRP]
GO

/****** Object:  View [dbo].[v_ReservationData]    Script Date: 11/27/2014 18:39:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[v_ReservationData]
AS
SELECT     g.STARTDT AS STARTTIME, g.ENDDT AS ENDTIME, g.DURATION, gs.ESTDELD AS PICKUP_DATE, gs.ESTDELTIME AS PICKUP_TIME, 
                      gs.WRKORDNO AS WORK_ORDER_ID, gs.STYPE AS TRANSACTION_TYPE, gs.CONFNAME, gs.CONLNAME, gs.UWTEL AS CONTACT_TEL, 
                      gs.UEMAIL AS CONTACT_EMAIL, s.NAME AS SNAME, s.SMANID, c.C2 AS TRANSACTION_NOTE, v.LICNO AS LIC_NO, v.MAKE, v.MODEL, v.SUBMODEL, 
                      ct.CUSTNO AS CUSTOMERNO, ct.FNAME, ct.LNAME, gr.NOTE AS WORK_DESCRIPTION, c.C2 AS DEPT, c.C1 AS DEPTID
FROM         dbo.CORW AS c INNER JOIN
                      dbo.GSAL AS gs ON c.C1 = gs.STYPE INNER JOIN
                      dbo.VEHI AS v ON gs.VEHIID = v.VEHIID INNER JOIN
                      dbo.CUST AS ct ON gs.CUSTNO = ct.CUSTNO INNER JOIN
                      dbo.GTRES AS g INNER JOIN
                      dbo.SMAN AS s ON g.RESID = s.SMANID ON gs.RECEIVER = s.SMANID INNER JOIN
                      dbo.GROW AS gr ON gs.GSALID = gr.GSALID
WHERE     (c.CODAID = 'LIIKETAPTY') AND (c.C7 LIKE '%K%') AND (gr.RTYPE = 7)
